/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package dao;


/**
 *
 * @author rog
 */
import com.sun.xml.bind.v2.model.core.ID;
import java.lang.String;
import java.awt.BorderLayout;
import java.awt.CardLayout;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseEvent;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.Date;
import javax.swing.JOptionPane;
import javax.swing.table.TableModel;
import javax.swing.table.DefaultTableModel;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JButton;
import javax.swing.JDialog;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JTextField;
import javax.swing.event.TableModelEvent;
import javax.swing.event.TableModelListener;
import module.Student;
import org.apache.poi.hssf.record.CFRuleBase;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import test.BarChart;
import test.TestData;
import util.JDBCUtil;
import util.MyRowMapper;
import javax.swing.filechooser.FileFilter;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.swing.JOptionPane;
import java.awt.Font;
import java.awt.event.WindowEvent;
import java.awt.event.WindowListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JDialog;
import javax.swing.JFrame;
import javax.swing.JOptionPane;
import javax.swing.WindowConstants;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartPanel;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.axis.CategoryAxis;
import org.jfree.chart.axis.ValueAxis;
import org.jfree.chart.labels.ItemLabelAnchor;
import org.jfree.chart.labels.ItemLabelPosition;
import org.jfree.chart.labels.StandardCategoryItemLabelGenerator;
import org.jfree.chart.plot.CategoryPlot;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.chart.renderer.category.BarRenderer;
import org.jfree.data.category.CategoryDataset;
import org.jfree.data.category.DefaultCategoryDataset;
import org.jfree.ui.TextAnchor;


public class StudentDaoImpl implements StudentDao {

    public List<Student> updateTable() {
        JdbcTemplate template=new JdbcTemplate(JDBCUtil.getDatasource());
        String sql="select * from student";               
        return template.query(sql,new BeanPropertyRowMapper<Student>(Student.class));
             
    }
    
    public Student getStudent() {
	
        JdbcTemplate template=new JdbcTemplate(JDBCUtil.getDatasource());       
        String sql="INSERT INTO student (NAME,gender,birthday,math,java,sports,totalScore)VALUES(?,?,?,?,?,?,?)";
        String getLastStudentSql = "select * from student order by id DESC limit 1;";   
        return template.queryForObject(getLastStudentSql, new MyRowMapper());    //获取表中最后一条数据
    }
    public boolean addStudent(Student s) {
        JdbcTemplate template=new JdbcTemplate(JDBCUtil.getDatasource());
        try{
        template.update("INSERT INTO student (id,NAME,gender,birthday,math,java,sports,totalScore)VALUES(?,?,?,?,?,?,?,?)",
                s.getId(),s.getName(),s.getGender(),s.getBirthday(),0,0,0,0);
        }catch(Exception e){
            JOptionPane.showMessageDialog(null, "数据有误");
            return false;
        }
        return true;
    }

    public boolean updateDatabase(TableModelEvent e,TableModel cartModel) {
        //TableModel cartModel = jTable2.getModel();
        int column = e.getColumn();

        Connection conn = null;
        Statement pstmt = null;
        ResultSet rs = null;
        String URL = "jdbc:mysql://localhost:3306/stu?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC";
        String driverName = "com.mysql.jdbc.Driver";
        String userName = "root";// root
        String password = "123";// 123

        if (column >= 5) {
            String grade = (String) cartModel.getValueAt(e.getFirstRow(), column);
            String id = (String) cartModel.getValueAt(e.getFirstRow(), 1);
            String temp = (String) cartModel.getColumnName(column);
            String course="";
            String totalScore;
            try{
                double temp_score=Double.valueOf(String.valueOf(cartModel.getValueAt(e.getFirstRow(), 5)))+Double.valueOf(String.valueOf(cartModel.getValueAt(e.getFirstRow(), 6)))
                        +Double.valueOf(String.valueOf(cartModel.getValueAt(e.getFirstRow(), 7)));
                totalScore=String.valueOf(temp_score);
            }catch(Exception ex){
                JOptionPane.showMessageDialog(null, "输入有误");
                return false;
            }
            
            if(temp.equals("数学")){
                course="math";
            }else if(temp.equals("体育")){
                course="sports";
            }else{
                course="java";
            }
            try {
                conn = DriverManager.getConnection(URL, userName, password);
                pstmt=conn.createStatement();
                //String sql = ""+"update student set balance='"+course+"' where id='"+id+"'"; 
                String sql1= ""+"update student set "+course+"="+grade+" where id="+id;
                String sql2= ""+"update student set totalScore"+"="+totalScore+" where id="+id;
                pstmt.executeUpdate(sql1);
                pstmt.executeUpdate(sql2);
                
            } catch (SQLException er) {
                er.printStackTrace();
                return false;
            } catch (Exception er) {
                er.printStackTrace();
                return false;
            } finally {
                if (pstmt != null) {
                    try {
                        pstmt.close();
                        pstmt = null;
                    } catch (SQLException er) {
                        er.printStackTrace();
                    }

                }
                if (conn != null)
                try {
                    conn.close();
                    conn = null;
                } catch (SQLException er) {
                    er.printStackTrace();
                }

            }
        }
        return true;
    }

    public void getTestData(DefaultTableModel cartModel){
        //产生测试数据 
        JdbcTemplate template=new JdbcTemplate(JDBCUtil.getDatasource());       
        String sql="INSERT INTO student (NAME,gender,birthday,math,java,sports,totalScore)VALUES(?,?,?,?,?,?,?)";        
        int num = Integer.parseInt(JOptionPane.showInputDialog("请输入人数：", 100));
        List<Object[]> batchArgs=new ArrayList<>();
        Object[]str=new Object[8];
        //DefaultTableModel cartModel = (DefaultTableModel) jTable2.getModel();
        try{
            File writeName = new File("d:\\output.txt");
            writeName.createNewFile();
            try (FileWriter writer = new FileWriter(writeName);
                BufferedWriter out = new BufferedWriter(writer)) {
                for (int i =1; i <= num; i++) { 
                    String gender=TestData.getGender();
                    String name=TestData.getName(gender);
                    String birthday=TestData.getBirthday();
                    double math=TestData.getScore();
                    double java =TestData.getScore();
                    double sports=TestData.getScore();
                    double totalScore=math+java+sports;           
                    str[0]=true;
                    str[1]=String.format("2021%07d",i);
                    str[2]=name;
                    str[3]=gender;
                    str[4]=birthday;
                    str[5]=math;
                    str[6]=java;
                    str[7]=sports;
                    cartModel.addRow(str);
                    
                    out.write(String.format("2021%07d", i)+","+name+","+gender+","+birthday+","+math+","+java+","+sports+","+totalScore+"\r\n");
                    out.flush();           
                    batchArgs.add(new Object[]{name,gender,birthday,math,java,sports,totalScore});  
                }
            }
        }catch(IOException e){
            JOptionPane.showMessageDialog(null, "生成数据时发生错误");
        }
        template.batchUpdate(sql, batchArgs);                                               
    }

    public void deleteSelected(List<Object[]> batchArgs){
        JdbcTemplate template=new JdbcTemplate(JDBCUtil.getDatasource());
        String sql="delete from student where id = ?";       
         template.batchUpdate(sql,batchArgs);     
    }

    public void clearAll(){
        JdbcTemplate template=new JdbcTemplate(JDBCUtil.getDatasource());
        template.update("TRUNCATE TABLE student");
        template.update("ALTER TABLE student AUTO_INCREMENT=20210000001");
    }

    public void deleteById(String id){
        JdbcTemplate template=new JdbcTemplate(JDBCUtil.getDatasource());       
        String sql="delete from student where id = ?";
        template.update(sql,id);
    }

    public List<Student> query (){
        JdbcTemplate template=new JdbcTemplate(JDBCUtil.getDatasource());
        String sql="select * from student order by totalScore desc";               
        return template.query(sql,new BeanPropertyRowMapper<>(Student.class));      
    }
    
    public void queryById(javax.swing.JTextArea resultTextArea1,String input_id){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String URL = "jdbc:mysql://localhost:3306/stu?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC";
        String driverName = "com.mysql.jdbc.Driver";
        String sql1 = "select * from student where id = ?";
        String userName = "root";// root
        String password = "123";// 123
        int flag = 0;
        //String input_id = inputJTextField.getText();
        String sql2 = "select * from student";
        PreparedStatement pstmt2 = null;

        try {
            conn = DriverManager.getConnection(URL, userName, password);
            pstmt = conn.prepareStatement(sql1);
            pstmt.setString(1, input_id);
            rs = pstmt.executeQuery();

            pstmt = conn.prepareStatement(sql2);
            ResultSetMetaData rsmd = pstmt.getMetaData();
            int size = rsmd.getColumnCount();
            int i = 0;

            while (rs.next()) {
                flag = 1;
                resultTextArea1.setText(rsmd.getColumnName(i + 1) + ":" + rs.getString(i + 1) + "    " + rsmd.getColumnName(i + 2) + ":" + rs.getString(i + 2) + "\n");
                resultTextArea1.append(rsmd.getColumnName(i + 3) + ":" + rs.getString(i + 3) + "    " + rsmd.getColumnName(i + 4) + ":" + rs.getString(i + 4) + "\n");
                i += 4;
                resultTextArea1.append("------------该学生的成绩情况如下------------" + "\n");
                while (i < size) {
                    resultTextArea1.append(rsmd.getColumnName(i + 1) + ":" + rs.getString(i + 1) + "\n");
                    i++;
                }
                System.out.println(rs.getString(1) + "----" + rs.getString(2) + "---" + rs.getString(3));

            }
            resultTextArea1.setEditable(false);
            if (flag == 0) {
                JOptionPane.showMessageDialog(null, "查询不到该学号对应的学生");
            }
            System.out.println(rs);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (pstmt != null) {
                try {
                    pstmt.close();
                    pstmt = null;
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }
            if (conn != null)
                try {
                conn.close();
                conn = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }

    public void getBarChart(String input_id){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String URL = "jdbc:mysql://localhost:3306/stu?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC";
        String driverName = "com.mysql.jdbc.Driver";
        String sql1 = "select * from student where id = ?";
        String userName = "root";// root
        String password = "123";// 123456
        //String input_id = inputJTextField.getText();
        List<String> grade = new ArrayList<>();
        int flag = 0;

        try {
            conn = DriverManager.getConnection(URL, userName, password);
            pstmt = conn.prepareStatement(sql1);
            pstmt.setString(1, input_id);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                grade.add(rs.getString(5));
                grade.add(rs.getString(6));
                grade.add(rs.getString(7));
                flag = 1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (pstmt != null) {
                try {
                    pstmt.close();
                    pstmt = null;
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }
            if (conn != null)
                try {
                conn.close();
                conn = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (flag == 0) {
            JOptionPane.showMessageDialog(null, "查询不到该学号对应的学生");
        } else {
            JFrame j = new JFrame();
            JDialog jd = new JDialog();
            jd.setBounds(50, 50, 600, 600);
            jd.add(new BarChart("该生",Double.valueOf(grade.get(0)), Double.valueOf(grade.get(1)), Double.valueOf(grade.get(2))).getChartPanel());
            jd.setVisible(true);
        }
    }

    public boolean login(String inputAccount,String inputPassword){
        String URL = "jdbc:mysql://localhost:3306/stu?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC";
        String driverName = "com.mysql.jdbc.Driver";
        String sql = "select * from user";
        String userName = "root";// root
        String password = "123";// 123456
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        //String inputAccount = userNameTextField.getText();
        //String inputPassword = code(pwdPasswordField.getText());
        String line = "";
        boolean flag = false;
        //System.out.println("GUI.LoginJFrame.loginButtonActionPerformed()"+inputPassword);
        if (inputAccount.equals("") || inputPassword.equals("")) {
            JOptionPane.showMessageDialog(null, "用户名或密码不能为空");
            return flag;
        }
        try {
//            Class.forName(driverName);
            conn = DriverManager.getConnection(URL, userName, password);
            statement = conn.createStatement();
            resultSet = statement.executeQuery(sql);
            // id | stuno | name | gender | birthdate | major
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String password_user=resultSet.getString("password");
                //System.out.println("GUI.LoginJFrame.loginButtonActionPerformed()"+password_user);
                if(String.valueOf(id).equals(inputAccount)&&password_user.equals(inputPassword)){
                    flag = true;
                    break;
                }
               // System.out.println("id=" + id + " name=" + name + " password=" + password_user);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                    statement = null;
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }
            if (conn != null)
                try {
                    conn.close();
                    conn = null;
                } catch (SQLException e) {
                    e.printStackTrace();
                } 
        }
        return flag;
    }

    public void registered(String inputAccount,String inputPassword){
        // TODO add your handling code here:
        String URL = "jdbc:mysql://localhost:3306/stu?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC";
        String driverName = "com.mysql.jdbc.Driver";
        String sql = "insert into user(name,password) values(?,?)";
        String userName = "root";// root
        String password = "123";// 123456
        Connection conn = null;
        Statement statement = null;
        
        String line = "";
        boolean flag=false;
         try {
            //Class.forName(driverName);
            conn = DriverManager.getConnection(URL, userName, password);
            
            PreparedStatement pst = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS); //Statement.RETURN_GENERATED_KEYS可获得生成的主键
	    pst.setString(1, inputAccount);
	    pst.setString(2, inputPassword);
            pst.executeUpdate();
            
            ResultSet r=pst.getGeneratedKeys();
            if(r.next()){
                System.out.println("生成的主键是:"+r.getInt(1));
                JOptionPane.showMessageDialog(null, "账号(用此登录)为:"+r.getInt(1)+"\n"+"昵称为:"+inputAccount);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                    statement = null;
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }
            if (conn != null)
                try {
                    conn.close();
                    conn = null;
                } catch (SQLException e) {
                    e.printStackTrace();
                } 
        }
    }

    
}
